﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Npgsql;

namespace frmPrincipal
{
    public partial class frmConPorData : Form
    {
        private StandardDBConnection standardDBConnection = new StandardDBConnection();
        private string tabela;

        public frmConPorData(string title, string tabela, int grdX, int grdY)
        {
            Size grdSize = new Size(grdX, grdY - 70);
            Size frmSize = new Size(grdX + 40, grdY + 50);

            InitializeComponent();

            grdConsultaPorNome.Size = grdSize;
            this.Size = frmSize;
            
            btnSearch.Location = new Point(dtpFinal.Location.X + dtpFinal.Width + +  5, btnSearch.Location.Y);

            this.Text = title;
            this.tabela = tabela;            
        }

        //Funções Auxiliares
        private void reSize()
        {            
            grdConsultaPorNome.Size = new Size(this.Size.Width - 40, this.Size.Height - 120);
        }

        //Funções do Form
        private void frmConPorData_Resize(object sender, EventArgs e)
        {
            reSize();
        }
        private void frmConPorData_FormClosed(object sender, FormClosedEventArgs e)
        {
            standardDBConnection.cnn.Close();
        } 

        //Botões
        private void btnSearch_Click(object sender, EventArgs e)
        {
            string sqlCommandText = "";
            NpgsqlDataAdapter da;
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            string inicial = dtpInicial.ToString().Substring(43, 11);
            string final = dtpFinal.ToString().Substring(43, 11);

           

            try
            {
                if (tabela == "fornece")
                {
                    sqlCommandText = "SELECT data, quantidade, nome_forn, nome_prod, preco_custo " +
                        "FROM fornece INNER JOIN produto ON (cod_produto = cod_prod) " +
                        "INNER JOIN fornecedor ON (cnpj_forn = cnpj) " +
                            " WHERE data BETWEEN '" + inicial +
                            "' AND '" + final + "' " +
                            "ORDER BY data DESC";

                }
                else if (tabela == "venda")
                {                    
                    sqlCommandText = "SELECT dt_venda, venda.cod_venda, nome_atend, nome_cliente, nome_prod, SUM(quantidade*preco_venda) AS valor_total " +
                            "FROM venda INNER JOIN contem ON (venda.cod_venda = contem.cod_venda) " +
                                        "INNER JOIN atendente ON (cod_atendente = cod_atend) " +
                                        "INNER JOIN cliente ON (cpf_cliente = cpf) " +
                                        "INNER JOIN produto ON (cod_produto = cod_prod) " +
                            "GROUP BY venda.cod_venda, nome_atend, nome_cliente, nome_prod " +
                            "HAVING dt_venda BETWEEN '" + inicial + "' AND '" + final + "' " +
                            "ORDER BY venda.cod_venda DESC";
                }

                da = new NpgsqlDataAdapter(sqlCommandText, standardDBConnection.cnn);
                ds.Reset();
                da.Fill(ds);
                dt = ds.Tables[0];
                grdConsultaPorNome.DataSource = dt;

            }
            catch
            {
                MessageBox.Show("Erro.", this.Text, MessageBoxButtons.OK,MessageBoxIcon.Error);
            }
        }
    }
}
